USE [QLLVANTAI]
GO

/****** Object:  StoredProcedure [dbo].[sp_XemLichVanChuyen_theotuyenduong]    Script Date: 09/27/2013 23:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE proc [dbo].[sp_XemLichVanChuyen_theotuyenduong]
		@matuyenduong int, @error nvarchar(100) out
as
	begin tran
	set tran isolation level SERIALIZABLE
	
	-- kiem tra xem co ton tai lich di theo tuyen duong nay ko
	if exists (select * from LichVanChuyen where Ma_TuyenDuong = @matuyenduong)
	begin
		--declare cur cursor dynamic for
		--waitfor delay '00:00:10'
			select LVC.Ma_VanChuyen, Diem_XuatPhat,Diem_Den ,CTL.Ma_Xe, Khoang_Cach,Gio_Di, Gio_Den , CTL.So_TaiXeTrong, CTL.So_PhuLaiTrong  
			from ChiTietLich CTL, LichVanChuyen LVC, TuyenDuong TD
			where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen and LVC.Ma_TuyenDuong = @matuyenduong and LVC.Ma_TuyenDuong = TD.Ma_TuyenDuong and LVC.Gio_Di > GETDATE()
			order by Diem_XuatPhat
			
			commit tran
			
		--open cur
			
		--close cur
		--deallocate cur
	end
	
	else
	begin
		set @error = N'Lỗi: không tồn tại lịch đi với tuyến đường này'
		rollback
		print @error 
		return
	end
GO

